import pymysql
import configparser
from flask import current_app

class MySQL:
    def __init__(self, config_path='./conf/config.ini'):
        self._load_config(config_path)
        self.conn = None

    def _load_config(self, path):
        try:
            cf = configparser.ConfigParser()
            cf.read(path, encoding='utf-8')
            self.host = cf.get('db', 'host')
            self.user = cf.get('db', 'user')
            self.password = cf.get('db', 'password')
            self.database = cf.get('db', 'database')
            # self.port = cf.getint('db', 'port', fallback=3306)
            # self.charset = cf.get('db', 'charset', fallback='utf8mb4')
        except Exception as e:
            current_app.logger.error(f"[MySQL] 配置加载失败: {e}")
            raise

    def _connect(self):
        if self.conn is None or not self.conn.open:
            try:
                self.conn = pymysql.connect(
                    host=self.host,
                    user=self.user,
                    password=self.password,
                    database=self.database,
                    # port=self.port,
                    # charset=self.charset,
                    cursorclass=pymysql.cursors.DictCursor
                )
            except Exception as e:
                current_app.logger.error(f"[MySQL] 数据库连接失败: {e}")
                raise

    def __enter__(self):
        self._connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.conn:
            self.conn.close()

    def query_all(self, sql, params=None):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params)
                return cursor.fetchall()
        except Exception as e:
            current_app.logger.error(f"[MySQL] query_all 失败: {e}\nSQL: {sql}\nParams: {params}")
            return []

    def query_one(self, sql, params=None):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params)
                return cursor.fetchone()
        except Exception as e:
            current_app.logger.error(f"[MySQL] query_one 失败: {e}\nSQL: {sql}\nParams: {params}")
            return None

    def execute(self, sql, params=None, return_last_id=False):
        try:
            with self.conn.cursor() as cursor:
                affected = cursor.execute(sql, params)
                last_id = cursor.lastrowid
                self.conn.commit()
                return last_id if return_last_id else affected
        except Exception as e:
            self.conn.rollback()
            current_app.logger.error(f"[MySQL] execute 失败: {e}\nSQL: {sql}\nParams: {params}")
            return None

    def execute_many_sqls(self, sql_list_with_params, return_affected_rows=False):
        affected_rows = []
        try:
            with self.conn.cursor() as cursor:
                for sql, params in sql_list_with_params:
                    affected = cursor.execute(sql, params)
                    affected_rows.append(affected)
                self.conn.commit()
            return affected_rows if return_affected_rows else True
        except Exception as e:
            self.conn.rollback()
            current_app.logger.error(f"[MySQL] execute_many_sqls 失败，已回滚: {e}")
            return False
